import os
import numpy as np
from openpyxl import load_workbook
class MxlsxWB:
    def __init__(self,workpath=os.getcwd(),filename=None):
        self.workpath=workpath   #默认在当前的目录
        self.filename=filename
    #设置工作目录
    def set_path(self,workpath):
        self.workpath=workpath
        os.chdir(self.workpath)
    #设置文件名
    def set_filename(self,filename=None):
        self.filename=filename
    #获取文件基本信息
    def get_fileinfo(self):
        print(self.filename)
        print('='*30,'文件信息','='*30) #分割线
        self.wb=load_workbook(filename=self.filename)
        self.sheetnames=self.wb.sheetnames
        print('文件'+self.filename+'共包含',len(self.sheetnames),'工作表')
        print('表名为：',end='')
        for name in self.sheetnames:
            print(name,'-',end='')
        print('\n')
        print('='*30,'文件信息结束','='*30) #分割线
    #选择工作表
    def choose_sheet(self,sheetname=None):
        if sheetname==None:
            self.sheetname=self.sheetname[0]
        else:
            self.sheetname=sheetname
        self.worksheet=self.wb[self.sheetname]
    #获取工作表基本信息
    def get_sheetinfo(self):
        print('='*30,self.sheetname,'='*30) #分割线
        self.num_of_rows=len(list(self.worksheet.rows))
        self.num_of_cols=len(list(self.worksheet.columns))

        print('行数：',self.num_of_rows)
        print('列数：',self.num_of_cols)
        print('列名：',MxlsxWB.get_rowdata(self,rownum=1))
        print('='*30,self.sheetname,'='*30) #分割线
    #获取工作表的总行数
    def get_rows_of_sheet(self):
        return len(list(self.worksheet.rows))
    #获取工作表的总列数
    def get_cols_of_sheet(self):
        return len(list(self.worksheet.columns))
    #获取具有某个字符串的行号
    def get_row_number_of_sheet(self,rownum,col_name):
        for row in self.worksheet.iter_rows(min_row=1,max_row=rownum,max_col=self.num_of_cols):
            for cell in row:
                if cell.value==col_name:
                    return cell.row
    #获取具有某个字符串的列号
    def get_col_number_fo_sheet(self,rownum,col_name):
        for row in self.worksheet.iter_rows(min_row=1,max_row=rownum,max_col=self.num_of_cols):
            for cell in row:
                if cell.value==col_name:
                    return cell.col_idx
    "'基于openpyxl进行数扰的查询与获取'"
    #获取单行数据
    def get_rowdata(self,rownum):
        rowdata=[]
        for row in self.worksheet.iter_rows(min_row=rownum,max_row=rownum,max_col=self.num_of_cols):
            for cell in row:
                rowdata.append(cell.value)
        print(rowdata)
        return rowdata
    #获取单列数据
    def get_coldata(self, colnum):
        coldata = []
        for col in self.worksheet.iter_cols(min_col=colnum,max_col=colnum,max_row=self.num_of_cols):
            for cell in col:
                coldata.append(cell.value)
        print(coldata)
        return coldata
    #获取单列数据（指定开始，结束行）
    def get_coldata(self,colnum,minrow,maxrow):
        coldata=[]
        for col in self.worksheet.iter_cols(min_col=colnum, max_col=colnum, min_row=minrow,max_row=maxrow):
            for cell in col:
                coldata.append(cell.value)
            print(coldata)
            return coldata
    #获取特定区域数据
    def get_areadata(self,min_row,max_row,min_col,max_col):
        print('='*30,'区域数据','='*30) #分割线
        #创建空白的（全为0）矩阵，数据类型指定为str
        areadata=np.matrix(np.zeros((max_row-min_row+1,max_col-min_col+1)),dtype=str)
        for col in self.worksheet.iter_cols(min_row=min_row,max_row=max_row,min_col=min_col,max_col=max_col):
            for cell in col:
                col_index=cell.col_idx
                row_index=cell.row
                areadata[row_index-min_row,col_index-min_col]=cell.value
                print(areadata)
                print('='*30,'区域数据','='*30) #分割线
                return areadata
Demo=MxlsxWB()
Demo.set_filename('score/cgtest.xlsx')
Demo=MxlsxWB(filename='score/cgtest.xlsx')
Demo.get_fileinfo()
Demo.choose_sheet('Sheet1')
Demo.get_sheetinfo()
# Demo.get_areadata(5,7,1,9)
Demo.get_coldata(2,0,9)
Demo.get_rowdata(5)



